package com.physique.tea.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.physique.tea.Pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper extends BaseMapper<User> {
    // ========== 增 ==========
    @Insert("INSERT INTO USERS (USERNAME, PASSWORD, LOCAL, SEX) " +
            "VALUES (#{USERNAME}, #{PASSWORD}, #{LOCAL}, #{SEX})")
    int insertUser(User user);

    // ========== 删 ==========
    @Delete("DELETE FROM USERS WHERE USERNAME = #{USERNAME}")
    int deleteByUsername(@Param("USERNAME") String USERNAME);

    @Delete("DELETE FROM USERS WHERE LOCAL = #{LOCAL}")
    int deleteByLocal(@Param("LOCAL") String LOCAL);

    // ========== 改 ==========
    @Update("UPDATE USERS SET " +
            "PASSWORD = #{PASSWORD}, " +
            "LOCAL = #{LOCAL}, " +
            "SEX = #{SEX} " +
            "WHERE USERNAME = #{USERNAME}")
    int updateUser(User user);

    @Update("UPDATE USERS SET PASSWORD = #{PASSWORD} WHERE USERNAME = #{USERNAME}")
    int updatePassword(@Param("USERNAME") String USERNAME, @Param("PASSWORD") String PASSWORD);

    @Update("UPDATE USERS SET LOCAL = #{LOCAL} WHERE USERNAME = #{USERNAME}")
    int updateLocal(@Param("USERNAME") String USERNAME, @Param("LOCAL") String LOCAL);

    @Update("UPDATE USERS SET SEX = #{SEX} WHERE USERNAME = #{USERNAME}")
    int updateSex(@Param("USERNAME") String USERNAME, @Param("SEX") String SEX);

    // ========== 查 ==========
    @Select("SELECT USERNAME, PASSWORD, LOCAL, SEX FROM USERS WHERE USERNAME = #{USERNAME}")
    User selectByUsername(@Param("USERNAME") String USERNAME);

    @Select("SELECT USERNAME, PASSWORD, LOCAL, SEX FROM USERS WHERE LOCAL = #{LOCAL}")
    List<User> selectByLocal(@Param("LOCAL") String LOCAL);

    @Select("SELECT USERNAME, PASSWORD, LOCAL, SEX FROM USERS WHERE SEX = #{SEX}")
    List<User> selectBySex(@Param("SEX") String SEX);

    @Select("SELECT USERNAME, LOCAL, SEX FROM USERS WHERE USERNAME LIKE '%' || #{KEYWORD} || '%' " +
            "OR LOCAL LIKE '%' || #{KEYWORD} || '%'")
    List<User> searchUsers(@Param("KEYWORD") String KEYWORD);

    @Select("SELECT USERNAME, LOCAL, SEX FROM USERS")
    List<User> selectAllBasicInfo();

    // ========== 统计查询 ==========
    @Select("SELECT LOCAL, COUNT(*) AS USER_COUNT FROM USERS GROUP BY LOCAL")
    List<Map<String, Object>> countUsersByLocal();

    @Select("SELECT SEX, COUNT(*) AS GENDER_COUNT FROM USERS GROUP BY SEX")
    List<Map<String, Object>> countUsersBySex();

    @Select("SELECT LOCAL, SEX, COUNT(*) AS COUNT FROM USERS GROUP BY LOCAL, SEX")
    List<Map<String, Object>> countUsersByLocalAndSex();

    // ========== 关联查询 ==========
    @Select("SELECT u.USERNAME, u.LOCAL, u.SEX, COUNT(o.ORDERID) AS ORDER_COUNT " +
            "FROM USERS u LEFT JOIN ORDERS o ON u.USERNAME = o.USERNAME " +
            "GROUP BY u.USERNAME, u.LOCAL, u.SEX")
    List<Map<String, Object>> countOrdersPerUser();
}